import numpy as np
from datetime import datetime, timedelta
import pandas as pd
import random


# 1、生成随机销售数据
def generate_sample_data(num_records=1000):
    # 设置随机种子，包拯可重复性
    np.random.seed(42)

    # 生成日期范围
    end_date = datetime.now()
    start_date = end_date - timedelta(days=365)
    dates = pd.date_range(start=start_date, end=end_date, periods=num_records)

    # 示例数据
    products = ['笔记本电脑', '手机', '平板电脑', '智能手表', '耳机']
    salespeople = ['张三', '李四', '王五', '赵六', '钱七']

    # 产品价格范围
    price_ranges = {
        '笔记本电脑': (4000, 12000),
        '手机': (2000, 8000),
        '平板电脑': (2500, 6000),
        '智能手表': (800, 3000),
        '耳机': (200, 2000)
    }

    # 生成随机数据
    data = {
        '销售日期': dates,
        '产品名称': [random.choice(products) for _ in range(num_records)],
        '销售员': [random.choice(salespeople) for _ in range(num_records)],
        '销售数量': np.random.randint(1, 10, num_records)
    }

    # 根据 产品价格范围生成随机价格
    data['单价'] = [random.uniform(*price_ranges[product]) for product in data['产品名称']]

    # 创建DataFrame
    df = pd.DataFrame(data)

    # 按日期排序
    df = df.sort_values('销售日期')

    return df


# 2、保存为Excel文件
def save_to_excel(df, file_name='sales_data.xlsx'):
    with pd.ExcelWriter(file_name, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name='销售数据', index=False, freeze_panes=(1, 0))

        # 获取工作表对象
        worksheet = writer.sheets['销售数据']

        # 设置列宽
        worksheet.column_dimensions['A'].width = 20  # 销售日期
        worksheet.column_dimensions['B'].width = 15  # 产品名称
        worksheet.column_dimensions['C'].width = 12  # 销售员
        worksheet.column_dimensions['D'].width = 12  # 销售数量
        worksheet.column_dimensions['E'].width = 12  # 单价


# 3、生成数据并保存
def main():
    try:
        # 生成示例数据
        df = generate_sample_data(num_records=1000)

        # 保存到excel
        save_to_excel(df)

        print("示例销售数据已生成并保存到 sales_data.xlsx")

        # 显示数据预览
        print("\n数据预览")
        print(df.head())

        # 显示基本统计信息
        print("\n基本统计信息")
        print(df.describe())

        # 显示每个销售员的销售总额
        print("\n每个销售员的销售情况")
        sales_summary = df.groupby('销售员').agg({
            '销售数量': 'sum',
            '单价': lambda x: (x * df.loc[x.index, '销售数量']).sum()
        }).round(2)
        sales_summary.columns = ['销售数量', '销售额']
        print(sales_summary)
    except Exception as e:
        print(f"生产数据过程中出现错误: {e}")

if __name__ == '__main__':
    main()
